package com.poi;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {
	public static boolean replaceModelOld(Map item, String sourceFilePath,
			String targetFilePath) {
		boolean bool = true;
		try {
			FileInputStream fis = new FileInputStream(sourceFilePath);

			XSSFWorkbook wb = new XSSFWorkbook(fis);
			int sheet_numbers = wb.getNumberOfSheets();
			for (int j = 0, size = sheet_numbers; j < sheet_numbers; j++) {
				XSSFSheet sheet = wb.getSheetAt(j);
				Iterator rows = sheet.rowIterator();
				while (rows.hasNext()) {
					XSSFRow row = (XSSFRow) rows.next();
					if (row != null) {
						int num = row.getLastCellNum();
						for (int i = 0; i < num; i++) {
							XSSFCell cell = row.getCell(i);
							if (cell != null) {
								cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							}
							if (cell == null
									|| cell.getStringCellValue() == null) {
								continue;
							}
							String value = cell.getStringCellValue();

							if (!"".equals(value)) {
								Set<String> keySet = item.keySet();
								Iterator<String> it = keySet.iterator();
								while (it.hasNext()) {
									String text = it.next();
									if (value.equalsIgnoreCase(text)) {
										cell.setCellValue((String) item
												.get(text));
										break;
									}
								}
							} else {
								cell.setCellValue("");
							}
						}
					}
				}
			}

			// 输出文件
			FileOutputStream fileOut = new FileOutputStream(targetFilePath);
			wb.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			bool = false;
			e.printStackTrace();
		}
		return bool;
	}

	public static boolean replaceModel(Map item, String sourceFilePath,
			String targetFilePath) {
		boolean bool = true;
		try {
			FileInputStream fis = new FileInputStream(sourceFilePath);

			HSSFWorkbook wb = new HSSFWorkbook(fis);
			
			int sheet_numbers = wb.getNumberOfSheets();
			for (int j = 0, size = sheet_numbers; j < sheet_numbers; j++) {
				HSSFSheet sheet = wb.getSheetAt(j);
				Iterator rows = sheet.rowIterator();
				while (rows.hasNext()) {
					HSSFRow row = (HSSFRow) rows.next();
					if (row != null) {
						int num = row.getLastCellNum();
						for (int i = 0; i < num; i++) {
							HSSFCell cell = row.getCell(i);
							if (cell != null) {
								cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							}
							if (cell == null
									|| cell.getStringCellValue() == null) {
								continue;
							}
							String value = cell.getStringCellValue();

							if (!"".equals(value)) {
								Iterator<Map.Entry<String, String>> its = item
										.entrySet().iterator();
								while (its.hasNext()) {
									Map.Entry<String, String> entry = its
											.next();
									String key = entry.getKey();
									String values = entry.getValue();
									if (value.contains(key)) {
										String result = value.replaceAll(key,
												values);
										cell.setCellValue(result);
										break;
									}
								}

							} else {
								cell.setCellValue("");
							}
						}
					}
				}
			}

			// 输出文件
			FileOutputStream fileOut = new FileOutputStream(targetFilePath);
			wb.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			bool = false;
			e.printStackTrace();
		}
		return bool;
	}

	public static boolean replaceModel2(Map item, String sourceFilePath,
			String targetFilePath) {
		boolean bool = true;
		try {
			FileInputStream fis = new FileInputStream(sourceFilePath);

			XSSFWorkbook wb = new XSSFWorkbook(fis);
			
			int sheet_numbers = wb.getNumberOfSheets();
			for (int j = 0, size = sheet_numbers; j < sheet_numbers; j++) {
				XSSFSheet sheet = wb.getSheetAt(j);
				Iterator rows = sheet.rowIterator();
				while (rows.hasNext()) {
					XSSFRow row = (XSSFRow) rows.next();
					if (row != null) {
						int num = row.getLastCellNum();
						for (int i = 0; i < num; i++) {
							XSSFCell cell = row.getCell(i);
							if (cell != null) {
								cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							}
							if (cell == null
									|| cell.getStringCellValue() == null) {
								continue;
							}
							String value = cell.getStringCellValue();

							if (!"".equals(value)) {
								Iterator<Map.Entry<String, String>> its = item
										.entrySet().iterator();
								while (its.hasNext()) {
									Map.Entry<String, String> entry = its
											.next();
									String key = entry.getKey();
									String values = entry.getValue();
									if (value.contains(key)) {
										String result = value.replaceAll(key,
												values);
										cell.setCellValue(result);
										break;
									}
								}

							} else {
								cell.setCellValue("");
							}
						}
					}
				}
			}

			// 输出文件
			FileOutputStream fileOut = new FileOutputStream(targetFilePath);
			wb.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			bool = false;
			e.printStackTrace();
		}
		return bool;
	}
	
	// 测试

	public static void main(String[] args) {

		Map item = new HashMap();
		item.put("王永华", "于国华");
		item.put("程琴琴", "韩宁");
		item.put("吴国进", "李田");
		item.put("丁一焕", "郑朝军");
		item.put("王秀玲", "郑朝军");
		item.put("周长好", "王卫");
		item.put("高潮", "李大凤");
		item.put("蒲安乐", "熊雪莲");
		item.put("徐瑞瑞", "石俭");
		item.put("曹志琴", "汪瑶");
		item.put("尹颖", "曾龙梅");
		item.put("阚婉", "许彦平");
		item.put("鄢启茹", "张艺");
		item.put("程琴琴", "毛飞");
		item.put("王国才", "李才仙");
		item.put("罗文波", "吴斌");
		
		
		String path = "D:\\work_cx\\智博文档\\CMM4相关文档\\CMM4-2018-1-15-自动生成文档\\08试运行及验收（（郑朝军））\\事件记录工单_BBSZDAGV1.0（于国华）.xls";
		
		String path2 = "D:\\work_cx\\智博文档\\CMM4相关文档\\CMM4-2018-1-15-自动生成文档\\08试运行及验收（（郑朝军））\\事件记录工单_BBSZDAGV1.0（于国华副本）.xls";
		

		replaceModel(item, path, path2);
//		replaceModel2(item, path, path2);

	}
}
